﻿using System;
using System.Collections.Generic;
using System.Text;
using WMS.IDAL;
using WMS.Entity;
using WMS.DAL.DBUtility;
using System.Data.SQLite;
using System.Data;

namespace WMS.DAL.SQLite
{
    public class CategoryDB : ICategory
    {
        #region ICategory 成员

        #region
        private readonly string getAllSqlString = "select [CategoryId],[Name],[ParentId],[IsDeleted] from Category";
        private readonly string deleteSqlString = "delete from Category where CategoryId={0}";
        private readonly string insertSqlString = "insert into Category (Name,ParentId) values(@Name,@Pid)";
        private readonly string updateSqlString = "update Category set Name=@Name where CategoryId=@id";
        private readonly string maxIDSqlString = "select max(categoryid) from Category";
        private readonly string sqlSelectString = "select * from Category where CategoryId={0}";
        #endregion

        public bool Exists(int categoryId)
        {
            throw new NotImplementedException();
        }

        public int Add(WMS.Entity.CategoryEntity entity)
        {
            SQLiteParameter[] para = new SQLiteParameter[]
            {
                new SQLiteParameter("@Name",DbType.String),
                new SQLiteParameter("@Pid",DbType.Int32)
            };
            para[0].Value = entity.Name;
            para[1].Value = entity.ParentId;

            SQLiteHelper.ExecuteNonQuery(insertSqlString,para);

            return 1;
        }

        public void Update(WMS.Entity.CategoryEntity entity)
        {
            SQLiteParameter[] para = new SQLiteParameter[]
            {
                new SQLiteParameter("@Name",DbType.String),
                new SQLiteParameter("@id",DbType.Int32)
            };
            para[0].Value = entity.Name;
            para[1].Value = entity.CategoryId;

            SQLiteHelper.ExecuteNonQuery(updateSqlString, para);
        }

        public void Delete(int categoryId)
        {
            string sqlString = string.Format(deleteSqlString,categoryId);
            SQLiteHelper.ExecuteNonQuery(SQLiteHelper.ConnectionString,CommandType.Text,sqlString,null);
        }

        public WMS.Entity.CategoryEntity GetEntity(int categoryId)
        {
            IList<WMS.Entity.CategoryEntity> list = GetList(string.Format(sqlSelectString, categoryId));
            if (list.Count > 0)
            {
                return list[0];
            }
            return null;
        }

        public IList<WMS.Entity.CategoryEntity> GetList(string strWhere)
        {
            #region
            IList<CategoryEntity> entityList = new List<CategoryEntity>();

            using (SQLiteDataReader reader = SQLiteHelper.ExecuteReader(SQLiteHelper.ConnectionString, CommandType.Text, strWhere, null))
            {
                while (reader.Read())
                {
                    CategoryEntity entity = new CategoryEntity();

                    entity.CategoryId = int.Parse(reader["CategoryId"].ToString());
                    entity.Name = reader["Name"].ToString();
                    entity.ParentId = int.Parse(reader["ParentId"].ToString());
                    entity.IsDeleted = int.Parse(reader["IsDeleted"].ToString());

                    entityList.Add(entity);
                }

                if (reader.IsClosed == false)
                    reader.Close();
            }

            return entityList;
            #endregion
        }

        public IList<WMS.Entity.CategoryEntity> GetAllList()
        {
            return GetList(getAllSqlString);
        }

        public int GetMaxID()
        {
            object obj = SQLiteHelper.ExecuteScalar(CommandType.Text,maxIDSqlString,null);

            return int.Parse(obj.ToString());
        }

        #endregion
    }
}
